var Pool = require('../Tools/TestCenterPool').pool;
var _ = require('lodash');
var request = require('request');
var moment = require('moment');

class TestRecordModel {
  async insert(testRecord, user) {
    try {
      let sql = `INSERT INTO test_record (
            version_test_count, 
            new_feature_remark,update_remark, advise_remark, self_test_status, test_package_url,test_status,
            project_id, project_name,
            version_id, version_name,
            create_user_id, create_user_name, create_real_name
                 )
      SELECT
       (SELECT COUNT(*) FROM test_record AS r WHERE r.version_id=${testRecord.versionId} AND r.project_id=${testRecord.projectId} AND r.data_status=0)+1, 
            ?, ?, ?, ?, ?,?, 
            ${testRecord.projectId}, (SELECT p.name FROM test_project AS p WHERE p.id=${testRecord.projectId}),  
            ${testRecord.versionId}, (SELECT v.name FROM test_version AS v WHERE v.id=${testRecord.versionId}),
            ${user.id}, '${user.userName}', '${user.realName}'
      FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM test_record WHERE version_id=${testRecord.versionId} AND is_active=0)`
      let sql_params = new Array(5);
      sql_params[0] = testRecord.newFeatureRemark ? testRecord.newFeatureRemark : '';
      sql_params[1] = testRecord.updateRemark ? testRecord.updateRemark : '';
      sql_params[2] = testRecord.adviseRemark ? testRecord.adviseRemark : '';
      sql_params[3] = testRecord.selfTestStatus;
      sql_params[4] = testRecord.testPackageUrl ? testRecord.testPackageUrl : '';
      sql_params[5] = testRecord.testStatus? testRecord.testStatus:1;
      let result = await Pool.queryAsync(sql, sql_params);
      let testRecordId = 0;
      if (result.affectedRows == 1) {
        testRecordId = result.insertId;
        let update_version_sql = `UPDATE test_version SET record_count=record_count+'1' WHERE id=${testRecord.versionId}`;
        await Pool.queryAsync(update_version_sql);
      }
      return testRecordId;
    } catch (error) {
      return error;
    }
  }

  async updateInfo(testRecord) {
    try {
      let sql = `UPDATE test_record SET `;
      if (testRecord.newFeatureRemark) {
        sql += `new_feature_remark='${testRecord.newFeatureRemark}',`;
      }
      if (testRecord.updateRemark) {
        sql += `update_remark='${testRecord.updateRemark}',`;
      }
      if (testRecord.adviseRemark) {
        sql += `advise_remark='${testRecord.adviseRemark}',`;
      }
      if (testRecord.testPackageUrl) {
        sql += `test_package_url='${testRecord.testPackageUrl}',`;
      }
      sql = sql.substring(0, sql.lastIndexOf(','));
      sql += ` WHERE id=${testRecord.id} AND data_status=0 AND test_status IN (1,2) AND is_active=0`;

      let result = await Pool.queryAsync(sql);
      return result.affectedRows;
    } catch (error) {
      return error;
    }

  }

  async updateStatus(testRecordId, testStatus) {
    let lockTestStatus = 0;
    testStatus == 2 || testStatus == 3 ? lockTestStatus = 1 : 0;
    testStatus == 4 || testStatus == 5 ? lockTestStatus = 2 : 0;

    if (testStatus <= 1 || testStatus > 5 || lockTestStatus == 0) {
      return;
    }

    let sql = `UPDATE test_record SET test_status=${testStatus}`;
    if (testStatus == 3 || testStatus == 4 || testStatus == 5) {
      sql += `, is_active=1`;
    }
    sql += ` WHERE id=${testRecordId} AND test_status=${lockTestStatus} AND data_status=0`;
    let update_back_count_sql = `UPDATE test_version SET back_record_count=back_record_count+'1' WHERE id=(select version_id from test_record where id=${testRecordId})`
    try {
      let result = await Pool.queryAsync(sql);
      if (result.affectedRows !== 0) {
        if (testStatus == 3 || testStatus == 4) {
          await Pool.queryAsync(update_back_count_sql);
        }
      }
      return result.affectedRows;
    } catch (error) {
      return error;
    }
  }

  async updateVersionNameByVersionId(versionId, versionName){
    let sql = `UPDATE test_record SET version_name='${versionName}' WHERE version_id=${versionId}`;
    try {
      let result = await Pool.queryAsync(sql);
      return result.affectedRows ? true : false;
    } catch (error) {
      return error;
    }
  }

  async deleteById(testRecordId) {

    let sql = `UPDATE test_record SET data_status=1 WHERE id=${testRecordId} AND data_status=0 AND test_status IN (1,2)`;
    let update_version_sql = `UPDATE test_version SET record_count=record_count-'1' where test_version.id=(select version_id from test_record where id=${testRecordId})`
    try {
      let result = await Pool.queryAsync(sql);
      let flag = false;
      if (result.affectedRows == 1) {
        flag = true;
        await Pool.queryAsync(update_version_sql);
      }
      return flag;
    } catch (error) {
      throw error;
    }
  }

  async queryUnFinishedTestRecord(versionId) {
    let sql = `SELECT count(*) FROM test_record WHERE data_status=0
     AND is_active=0 AND (test_status IN (1,2)) AND version_id=${versionId}`;

    try {
      let result = await Pool.queryAsync(sql);
      let flag = true;
      result = result.map((r) => {
        return r['count(*)']
      })[0];
      flag = result > 0 ? true : false;
      return flag;
    } catch (error) {
      throw error;
    }
  }

  async queryTestRecordCountByProjectId(projectId){
    try {
      let sql = `SELECT * FROM test_record where project_id=${projectId} and data_status=0`;
      let result = await Pool.queryAsync(sql);
      return result.length;
    } catch (error) {
      throw error;
    }
  }

  async queryTestRecordCountByVersionId(versionId){
    try {
      let sql = `SELECT * FROM test_record where version_id=${versionId} and data_status=0`;
      let result = await Pool.queryAsync(sql);
      return result.length;
    } catch (error) {
      throw error;
    }
  }

  async queryTestRecordCreater(testRecordId) {
    try {
      let sql = `SELECT create_user_id from test_record WHERE id=${testRecordId}`;
      let result = await Pool.queryAsync(sql);
      if (result == []) {
        return 0;
      }
      return result[0]['create_user_id']
    } catch (error) {
      throw error;
    }
  }

  async queryProjectIdByTestRecordId(testRecordId) {
    try {
      let sql = `SELECT project_id from test_record WHERE id=${testRecordId}`;
      let result = await Pool.queryAsync(sql);
      if (result == []) {
        return 0;
      }
      return result[0]['project_id']
    } catch (error) {
      throw error;
    }
  }

  async getActiveTestRecordList(condition) {
    try {

      let sql = `SELECT * FROM 
      (
        SELECT  v.id AS version_id, v.name AS version_name, v.project_id, v.project_name, v.record_count, v.expect_test_time, 
        t_record.id AS test_record_id, t_record.create_time ,  IFNULL(t_record.test_status, 0) AS test_status  FROM 
        (
        SELECT v.*, p.name AS project_name FROM test_version AS v, test_project AS p WHERE v.project_id=p.id
        )
        AS v
        LEFT JOIN 
        (
        SELECT r.create_time, r.test_status, r.id, r.version_id FROM 
        (
        SELECT * FROM test_record WHERE data_status=0 ORDER BY create_time DESC
        ) AS r
        INNER JOIN (
        SELECT MAX(create_time) create_time ,version_id FROM test_record GROUP BY version_id
        ) AS a 
        ON r.create_time = a.create_time and r.version_id = a.version_id
        ) 
        AS t_record
        ON t_record.version_id = v.id 
        WHERE 1=1
      ) AS vr 
      ORDER BY version_id DESC
      `;

      let count_sql = `SELECT count(*)  FROM 
      (
        SELECT  v.id AS version_id, v.name AS version_name, v.project_id, v.project_name, v.record_count, v.expect_test_time, 
        t_record.id AS test_record_id, t_record.create_time,  IFNULL(t_record.test_status, 0) AS test_status  FROM 
        (
        SELECT v.*, p.name AS project_name FROM test_version AS v, test_project AS p WHERE v.project_id=p.id
        )
        AS v
        LEFT JOIN 
        (
        SELECT r.create_time, r.test_status, r.id, r.version_id FROM 
        (
        SELECT * FROM test_record WHERE data_status=0 ORDER BY create_time DESC
        ) AS r
        GROUP BY r.version_id   
        ) 
        AS t_record
        ON t_record.version_id = v.id 
        WHERE 1=1
      ) AS vr
      WHERE 1=1 
    `;

      // if (condition.projectId && _.isArray(condition.projectId)) {
      //   sql += ` AND vr.project_id in ( ${condition.projectId.join(',')} ) `;
      //   count_sql += ` AND vr.project_id in ( ${condition.projectId.join(',')} )`
      // }
      // if (condition.versionId && _.isArray(condition.versionId)) {
      //   sql += ` AND vr.version_id=${condition.versionId}`;
      //   count_sql += ` AND vr.version_id=${condition.versionId}`;
      // }
      // if (condition.testStatus && _.isArray(condition.testStatus)) {
      //   sql += ` AND vr.test_status in ( ${condition.testStatus.join(',')} )`;
      //   count_sql += ` AND vr.test_status in ( ${condition.testStatus.join(',')})`;
      // }
      // if(condition.sorter && condition.sorter.field && condition.sorter.order){
      //   let sorter = condition.sorter;
      //   if(sorter.field === 'testCreateTime'){
      //     sorter.field = 'create_time';
      //   }
      //   if(sorter.field === 'expectTestTime'){
      //     sorter.field = 'expect_test_time';
      //   }
      //   if(sorter.field === 'recordCount'){
      //     sorter.field = 'record_count';
      //   }
      //   let order = sorter.order === 'ascend' ? 'ASC' : sorter.order === 'descend' ? 'DESC':'';
      //   sql += ` ORDER BY ${sorter.field} ${order}`;
      //   count_sql += ` ORDER BY ${sorter.field} ${order}`;
      // }

      // sql += `  LIMIT ${(condition.page - 1) * condition.size}, ${condition.size}`;
      let rows = await Pool.queryAsync(sql);
      let count = await Pool.queryAsync(count_sql);
      count = count.map((c) => {
        return c['count(*)'];
      })[0];
      let recordlist = rows.map((r) => {
        return {
          projectId: r.project_id,
          projectName: r.project_name,
          versionId: r.version_id,
          versionName: r.version_name,
          recordCount: r.record_count,
          expectTestTime: r.expect_test_time,
          activeTestRecordId: r.test_record_id,
          testCreateTime: _.isNull(r.create_time) ? '0000-00-00' : r.create_time,
          testStatus: _.isNull(r.test_status) ? 0 : r.test_status
        }
      });
      return { activeRecordList: recordlist, count: count }
    } catch (error) {
      console.log(error)
      return error;
    }
  }

  async queryLastTestRecordByVersionId(versionId) {
    try {
      let sql = `SELECT * FROM test_record WHERE version_id=${versionId} AND data_status=0 ORDER BY version_test_count DESC`;
      let rows = await Pool.queryAsync(sql);
      if (!_.isEmpty(rows)) {
        let r = rows[0];
        let testCreateTime = moment(r.create_time).format('YYYY-MM-DD HH:mm:ss');
        let testRecord = {
          id: r.id,
          projectId: r.project_id,
          projectName: r.project_name,
          versionId: r.version_id,
          versionName: r.version_name,
          newFeatureRemark: r.new_feature_remark,
          updateRemark: r.update_remark,
          adviseRemark: r.advise_remark,
          testPackageUrl: r.test_package_url,
          selfTestStatus: r.self_test_status,
          createUserId: r.create_user_id,
          createUserName: r.create_user_name,
          createRealName: r.create_real_name,
          versionTestCount: r.version_test_count,
          testStatus: r.test_status,
          testCreateTime: testCreateTime
        }
        return testRecord;
      } else {
        return [];
      }
    } catch (error) {
      throw error;
    }
  }

  async queryFirstTestRecordByVersionId(versionId) {
    try {
      let sql = `SELECT * FROM test_record WHERE version_id=${versionId} AND data_status=0`;
      let rows = await Pool.queryAsync(sql);
      if (!_.isEmpty(rows)) {
        let r = rows[0];
        let testCreateTime = moment(r.create_time).format('YYYY-MM-DD HH:mm:ss');
        let testRecord = {
          id: r.id,
          project_id: r.project_id,
          project_name: r.project_name,
          versionId: r.version_id,
          versionName: r.version_name,
          newFeatureRemark: r.new_feature_remark,
          updateRemark: r.update_remark,
          adviseRemark: r.advise_remark,
          testPackageUrl: r.test_package_url,
          selfTestStatus: r.self_test_status,
          createUserId: r.create_user_id,
          createUserName: r.create_user_name,
          createRealName: r.create_real_name,
          versionTestCount: r.version_test_count,
          testStatus: r.test_status,
          testCreateTime: testCreateTime
        }
        return testRecord;
      } else {
        return [];
      }
    } catch (error) {
      throw error;
    }
  }

  async getRecordListByVersionId(versionId) {
    try {
      let sql = `SELECT id, version_test_count, is_active FROM test_record WHERE version_id=${versionId} AND data_status=0 ORDER BY version_test_count DESC `;
      let records = await Pool.queryAsync(sql);
      records = records.map((r) => {
        return {
          id: r.id,
          versionTestCount: r.version_test_count,
          isActive: r.is_active
        }
      })
      return records;
    } catch (error) {
      throw error;
    }
  }

  async queryTestRecordById(id){
    try {
      let sql = `SELECT * FROM test_record WHERE id=${id} and data_status=0`;
      let rows = await Pool.queryAsync(sql);
      if (!_.isEmpty(rows)) {
        let r = rows[0];
        let testCreateTime = moment(r.create_time).format('YYYY-MM-DD HH:mm:ss');
        let testRecord = {
          id: r.id,
          project_id: r.project_id,
          project_name: r.project_name,
          versionId: r.version_id,
          versionName: r.version_name,
          newFeatureRemark: r.new_feature_remark,
          updateRemark: r.update_remark,
          adviseRemark: r.advise_remark,
          testPackageUrl: r.test_package_url,
          selfTestStatus: r.self_test_status,
          createUserId: r.create_user_id,
          createUserName: r.create_user_name,
          createRealName: r.create_real_name,
          versionTestCount: r.version_test_count,
          testStatus: r.test_status,
          testCreateTime: testCreateTime
        }
        return testRecord;
      } else {
        return [];
      }
    } catch (error) {
      throw error;
    }
  }


  async httprequest(url,data){
    await request({
      url: url,
      method: "POST",
      json: true,
      headers: {
          "content-type": "application/json",
      },
      body: data
    }, function(error, response, body) {
      if (!error && response.statusCode == 200) {
          // console.log(body) // 请求成功的处理逻辑
      }
    });
  };

  async launchTestQuality(projectlist){
    const launchQuality = []
    for( var i=0;i< projectlist.length;i++){
      let p = projectlist[i]
      let launchcount_sql =`SELECT COUNT(*) as count FROM test_record WHERE project_id = ${p.id} AND (test_status = 3 OR test_status = 4 OR test_status =5)`
      let successcount_sql = `SELECT COUNT(*) as count FROM test_record WHERE project_id = ${p.id} AND test_status = 5`
      
      let launchcount = await Pool.queryAsync(launchcount_sql)
      let successcount = await Pool.queryAsync(successcount_sql)

      let launchtestquality = launchcount[0].count === 0? 0:(successcount[0].count/launchcount[0].count)*100
      let value = 0
      if(launchtestquality>=90){
        value = 5
      }else if(launchtestquality>=80){
        value = 4
      }else if(launchtestquality>=70){
        value = 3
      }else if(launchtestquality>=60){
        value = 2
      }else if(launchtestquality>0){
        value = 1
      }else{
        value = 0
      }
      // launchQuality.push({'name':p.name,'value':value})
      launchQuality.push({'name':p.name,'label':'提测质量','value':value})
    }
    return launchQuality
  }

  async getTestCount(pname){
    try{
      let sql = `SELECT test_count FROM (SELECT MAX(version_test_count)as test_count, project_name,version_id FROM test_record GROUP BY version_id
      )as c WHERE 1=1`
      if(pname !='all'){
        let sql_1 =` AND c.project_name ='${pname}'`
        sql += sql_1
      }

      let res = await Pool.queryAsync(sql)
      let count = 0
      res.map((r)=>{
        if(r.test_count){
          count +=r.test_count
        }
      })
      return count
    }catch(error){
      return error
    }
  
  }



  async launchTestSuccessRate(pname){
    let launchcount_sql =`SELECT COUNT(*) as count FROM test_record WHERE 1=1`
    let successcount_sql = `SELECT COUNT(*) as count FROM test_record WHERE test_status = 5`
    if(pname!='all'){
      let sql_1 =` AND test_record.project_name = '${pname}'`
      launchcount_sql+=sql_1
      successcount_sql +=sql_1
    }

    let launchcount = await Pool.queryAsync(launchcount_sql)
    let successcount = await Pool.queryAsync(successcount_sql)

    let rate = launchcount[0].count === 0? 0:(successcount[0].count/launchcount[0].count)

    rate = rate.toFixed(4)
    return rate
  }
  
  async queryVersionTestCount(testRecord){
    try{
      let sql =`SELECT test_status FROM test_record AS r WHERE r.version_id=${testRecord.versionId} AND r.project_id=${testRecord.projectId} AND r.data_status=0 ORDER BY version_test_count DESC LIMIT 0,1`
      let res = await Pool.queryAsync(sql)
      let data = 0
      if(res[0]){
         data = res[0].test_status
      } 
      return data
    }catch(error){
      return error
    }
  }

  async queryTestStatus(testRecordId){
    try{
      let sql = `SELECT project_id ,version_id FROM test_record WHERE id = ${testRecordId}`
      let res = await Pool.queryAsync(sql)
      if(res[0].project_id&&res[0].version_id){
        let sql1 =`SELECT COUNT(*) as count FROM test_record WHERE project_id = ${res[0].project_id} AND version_id = ${res[0].version_id} AND test_status = 5`
        let result = await Pool.queryAsync(sql1)
        return result[0].count
      }
      return -1
    }catch(error){
      return error
    }
  }

  
}

module.exports = new TestRecordModel();


